/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modelo.helper;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import modelo.DTO.DTODataMining;
import modelo.DTO.DTOVisitasAlumno;
import modelo.HibernateUtil;
import modelo.MdlUser;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @version 1.1
 * @author Ion B. Padilla
 */
public class UserHelper {
    private static UserHelper instance;

    public UserHelper() {
    }

    public static UserHelper getInstance() {
        if (null == instance) {
            instance = new UserHelper();
        }
        return instance;
    }
    /**
     * Metodo para conseguir un MdlUser por un nombre de alumno
     * @param nombre el nombre del alumno a convertir
     */
    public MdlUser getMdlUserByName(String nombre) {
		Transaction tx=null;
		MdlUser user = null;
		try{
			//Session session = HibernateUtil.getSessionFactory().getCurrentSession();
	        Session session = HibernateUtil.getInstance().getSession();
                tx=session.beginTransaction();
            Query q = session.createQuery("select from MdlUser where username='"+nombre+"'");
            if(q.list().size()!=0){
                user=(MdlUser)q.list().get(0);
                tx.commit();
                session.close();
                return user;
            }
            else{
                tx.commit();
                session.close();
                return null;
            }
        }catch(HibernateException e){
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas de las páginas de moodle por alumnos
     */
    public List<DTODataMining> getVisitasPorAlumnos() {
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(log.time) as numeroDeEntradas "
                    + "FROM MdlLog log, MdlUser user "
                    + "WHERE user.id=log.userid "
                    + "GROUP BY log.userid "
                    + "ORDER BY user.username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas por un alumno
     * @param nombre el nombre del alumno del que queremos saber las visitas
     */
    public DTODataMining getVisitasPorAlumno(String nombre) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(log.time) as numeroDeEntradas  FROM MdlLog log RIGHT JOIN MdlUser user WHERE user.id=log.userid AND username='"
                    + nombre + "' GROUP BY userid ORDER BY username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit();
            session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas de las páginas de moodle por curso
     * @param curso curso al que se va a consultar
     */
    public List<DTODataMining> getVisitasPorAlumnosPorCurso(int curso) {
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            //Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT usr.username, count(log.time) as numeroDeEntradas  "
                    + "FROM MdlCourse c, MdlLog log, MdlContext cx, MdlRoleAssignments ra, MdlRole r, MdlUser usr "
                    + "WHERE c.id = cx.instanceid "
                    + "AND usr.id=log.userid "
                    + "AND cx.id = ra.contextid "
                    + "AND ra.roleid = r.id "
                    + "AND ra.userid = usr.id "
                    + "AND r.shortname = 'student' "
                    + "AND cx.contextlevel = '50' and c.id="+curso+" "
                    + "GROUP BY log.userid "
                    + "ORDER BY usr.username "
                    ).list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }

    /**
     * Metodo para conseguir las visitas por un alumno y por curso
     * @param nombre el nombre del alumno del que queremos saber las visitas
     * @param curso curso al que se va a consultar
     */
    public DTODataMining getVisitasPorAlumnoPorCurso(String nombre, int curso) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            //Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(log.time) as numeroDeEntradas  FROM MdlLog log RIGHT JOIN MdlUser user WHERE user.id=log.userid AND log.course="+curso+" AND username='" + nombre + "' GROUP BY userid ORDER BY username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit();
            session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas únicas de las páginas de moodle por alumnos
     */
    public List<DTODataMining> getVisitasDifPorAlumnos() {
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(distinct log.url) as numeroDeEntradas  FROM MdlLog log, MdlUser user WHERE user.id=log.userid GROUP BY userid ORDER BY username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                lista.add(new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1])));
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas únicas por un alumno
     * @param nombre el nombre del alumno del que queremos saber las visitas únicas
     */
    public DTODataMining getDifVisitasPorAlumno(String nombre) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(distinct log.url) as numeroDeEntradas  FROM MdlLog log, MdlUser user WHERE user.id=log.userid AND username='" + nombre + "' GROUP BY userid ORDER BY username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit();
            session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas únicas de las páginas de moodle por curso
     * @param curso curso al que se va a consultar
     */
    public List<DTODataMining> getVisitasDifPorAlumnosPorCurso(int curso) {
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            //Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(distinct log.url) as numeroDeEntradas  FROM MdlLog log, MdlUser user WHERE user.id=log.userid AND log.course="+curso+" GROUP BY userid ORDER BY username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                lista.add(new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1])));
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir las visitas únicas por un alumno y por curso
     * @param nombre el nombre del alumno del que queremos saber las visitas únicas
     * @param curso curso al que se va a consultar
     */
    public DTODataMining getDifVisitasPorAlumnoPorCurso(String nombre, int curso) {
        DTOVisitasAlumno dtoVisitas = null;
        Transaction tx = null;
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        try {
            //Session session = HibernateUtil.getSessionFactory().getCurrentSession();
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(distinct log.url) as numeroDeEntradas  FROM MdlLog log, MdlUser user WHERE user.id=log.userid AND log.course="+curso+" AND username='" + nombre + "' GROUP BY userid ORDER BY username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                dtoVisitas = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit();
            session.close();
            return dtoVisitas;
        } catch (HibernateException e) {
            System.out.println(e.getMessage());
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir la lista de alumnos
     */
    public List<DTODataMining> getAlumnos() {
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            //Iterator results = session.createQuery("SELECT u.username,count(log.time) FROM MdlUser u, MdlLog log, MdlRole_assignments ra, MdlContext con, MdlCourse c, MdlRole  WHERE u.id = ra.userid AND ra.contextid = con.id AND con.contextlevel = 50 AND con.instanceid = c.id AND ra.roleid = r.id AND r.shortname = 'student' AND con.instanceid=6 AND u.id=log.userid  GROUP BY log.userid ").list().iterator();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(log.time) as numeroDeEntradas "
                    + "FROM MdlLog log, MdlUser user "
                    + "WHERE user.id=log.userid "
                    + "GROUP BY log.userid "
                    + "ORDER BY user.username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
                lista.add(alumno);
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir la lista de alumnos del curso
     * @param curso curso al que vamos a consultar
     */
    public List<DTODataMining> getAlumnosPorCurso(int curso) { //cambio la consulta por la otra
        ArrayList<DTODataMining> lista = new ArrayList<DTODataMining>();
        Transaction tx = null;
        try {
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            //Iterator results = session.createQuery("SELECT u.username,count(log.time) FROM MdlUser u, MdlLog log, MdlRole_assignments ra, MdlContext con, MdlCourse c, MdlRole  WHERE u.id = ra.userid AND ra.contextid = con.id AND con.contextlevel = 50 AND con.instanceid = c.id AND ra.roleid = r.id AND r.shortname = 'student' AND con.instanceid=6 AND u.id=log.userid  GROUP BY log.userid ").list().iterator();
            Iterator results = session.createQuery(
                    "SELECT usr.username "
                    + "FROM MdlCourse c , MdlContext cx, MdlRoleAssignments ra, MdlRole r, MdlUser usr "
                    + "WHERE c.id = cx.instanceid "
                    + "AND cx.id = ra.contextid "
                    + "AND ra.roleid = r.id "
                    + "AND ra.userid = usr.id "
                    + "AND r.shortname = 'student'"
                    + "AND cx.contextlevel = '50' and c.id="+curso+" "
//                    "SELECT user.username "
//                    + "FROM MdlLog log, MdlUser user "
//                    + "WHERE user.id=log.userid "
//                    + "AND log.course="+curso+" "
//                    + "GROUP BY log.userid "
//                    + "ORDER BY user.username"
                    ).list().iterator();
            while (results.hasNext()) {
                DTOVisitasAlumno alumno = new DTOVisitasAlumno((String) results.next(), "");
                lista.add(alumno);
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            e.getStackTrace();
            return null;
        }
    }
    /**
     * Metodo para conseguir la lista de alumnos en formato String
     */
    public List<String> getAlumnosString() {
        ArrayList<String> lista = new ArrayList<String>();
        Transaction tx = null;
        try {
            //Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username "
                    + "FROM MdlLog log, MdlUser user "
                    + "WHERE user.id=log.userid "
                    + "GROUP BY log.userid "
                    + "ORDER BY user.username").list().iterator();
            while (results.hasNext()) {
                lista.add((String)results.next());
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            e.getStackTrace();
            return null;
        }
    }
     /**
     * Metodo para conseguir la lista de alumnos del curso en formato String
      * @param curso curso al que vamos a consultar
     */
    public List<String> getAlumnosStringPorCurso(int curso) {
        ArrayList<String> lista = new ArrayList<String>();
        Transaction tx = null;
        try {
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT usr.username "
                   + "FROM MdlCourse c , MdlContext cx, MdlRoleAssignments ra, MdlRole r, MdlUser usr "
                    + "WHERE c.id = cx.instanceid "
                    + "AND cx.id = ra.contextid "
                    + "AND ra.roleid = r.id "
                    + "AND ra.userid = usr.id "
                    + "AND r.shortname = 'student'"
                    + "AND cx.contextlevel = '50' and c.id="+curso+" "
//                    "SELECT user.username "
//                    + "FROM MdlLog log, MdlUser user "
//                    + "WHERE user.id=log.userid "
//                    + "AND log.course="+curso+" "
//                    + "GROUP BY log.userid "
//                    + "ORDER BY user.username"
                    ).list().iterator();
            while (results.hasNext()) {
                lista.add((String)results.next());
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            e.getStackTrace();
            return null;
        }
    }

     public ArrayList<String> getAlumnosStringPorCursoLista(int curso) {
        ArrayList<String> lista = new ArrayList<String>();
        Transaction tx = null;
        try {
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT usr.username "
                   + "FROM MdlCourse c , MdlContext cx, MdlRoleAssignments ra, MdlRole r, MdlUser usr "
                    + "WHERE c.id = cx.instanceid "
                    + "AND cx.id = ra.contextid "
                    + "AND ra.roleid = r.id "
                    + "AND ra.userid = usr.id "
                    + "AND r.shortname = 'student'"
                    + "AND cx.contextlevel = '50' and c.id="+curso+" "
//                    "SELECT user.username "
//                    + "FROM MdlLog log, MdlUser user "
//                    + "WHERE user.id=log.userid "
//                    + "AND log.course="+curso+" "
//                    + "GROUP BY log.userid "
//                    + "ORDER BY user.username"
                    ).list().iterator();
            while (results.hasNext()) {
                lista.add((String)results.next());
            }
            tx.commit();
            session.close();
            return lista;
        } catch (HibernateException e) {
            e.getStackTrace();
            return null;
        }
    }


    /**
     * Metodo para conseguir un alumno pasandole el nombre
     * @param nombre el nombre del alumno
     */
    public DTODataMining getAlumno(String nombre) {
        Transaction tx = null;
        DTOVisitasAlumno alumno = null;
        try {
           // Session session = HibernateUtil.getSessionFactory().openSession();
            Session session = HibernateUtil.getInstance().getSession();
            tx = session.beginTransaction();
            Iterator results = session.createQuery(
                    "SELECT user.username,count(log.time) as numeroDeEntradas "
                    + "FROM MdlLog log, MdlUser user "
                    + "WHERE user.id=log.userid "
                    + "AND user.username='" + nombre + "'"
                    + "GROUP BY log.userid "
                    + "ORDER BY user.username").list().iterator();
            while (results.hasNext()) {
                Object[] row = (Object[]) results.next();
                alumno = new DTOVisitasAlumno((String) row[0], Long.toString((Long) row[1]));
            }
            tx.commit();
            session.close();
            return alumno;
        } catch (HibernateException e) {
            e.getStackTrace();
            return null;
        }
    }
}
